﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using Infraestrutura.Entidades;

namespace Infraestrutura.Repositorios.SQL
{
    public abstract class RepositorioSQL<T> where T: class, IEntidade, new()
    {
        private String _tabela;
        private String _ligacaoBD;

        protected String Tabela
        {
            get { return _tabela; }
            set { _tabela = value; }
        }

        protected String LigacaoBD
        {
            get { return _ligacaoBD; }
            set { _ligacaoBD = value; }
        }

        protected virtual T ObterPorChave(String parametros)
        {
            T item = null;

            using (SqlConnection sqlConnection = new SqlConnection(_ligacaoBD))
            {
                SqlCommand command = new SqlCommand(
                  String.Format("exec proc_{0}LoadByPrimaryKey {1}", _tabela, parametros),
                  sqlConnection);

                sqlConnection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.Read())
                {
                    item = new T();
                    item = CarregarDados(reader, item);
                }

                reader.Close();
            }

            return item;
        }

        public virtual IList<T> ObterTodos()
        {
            return ObterLista(String.Format("exec proc_{0}LoadAll",_tabela));
        }

        protected IList<T> ObterLista(String comando)
        {
            IList<T> lista = new List<T>();

            using (SqlConnection sqlConnection = new SqlConnection(_ligacaoBD))
            {
                SqlCommand command = new SqlCommand(
                  comando,
                  sqlConnection);

                sqlConnection.Open();

                SqlDataReader reader = command.ExecuteReader();

                T item = null;
                while (reader.Read())
                {
                    item = new T();
                    lista.Add(CarregarDados(reader, item));
                }

                reader.Close();
            }

            return lista;
        }



        public virtual T Actualiza(T item)
        {
            using (SqlConnection connection = new SqlConnection(_ligacaoBD))
            {
                SqlCommand command = new SqlCommand(
                    String.Format("exec proc_{0}Update {1}", _tabela, ObterParametrosActualizar(item)),
                    connection);
                
                command.Connection.Open();
                command.ExecuteScalar();
            }

            return item;
        }


        public virtual IList<T> Actualiza(IList<T> items)
        {
            using (SqlConnection connection = new SqlConnection(_ligacaoBD))
            {
                connection.Open();    
                SqlTransaction transaction = connection.BeginTransaction();

                SqlCommand command = null;
                foreach (T item in items)
                {
                    command = new SqlCommand(
                       String.Format("exec proc_{0}Update {1}", _tabela, ObterParametrosActualizar(item)),
                       connection);

                    command.ExecuteScalar();
                }
                transaction.Commit();
            }

            return items;
        }

        
        public virtual T Insere(T item)
        {
            using (SqlConnection connection = new SqlConnection(_ligacaoBD))
            {

                SqlCommand command = new SqlCommand(
                   String.Format("exec proc_{0}Insert {1}", _tabela, ObterParametrosInserir(item)),
                   connection);

                command.Connection.Open();
                item.AtribuirChave(command.ExecuteScalar());
            }

            return item;
        }

        public virtual IList<T> Insere(IList<T> items)
        {
            using (SqlConnection connection = new SqlConnection(_ligacaoBD))
            {
                connection.Open();    
                SqlTransaction transaction = connection.BeginTransaction();

                SqlCommand command = null;
                foreach (T item in items)
                {
                    command = new SqlCommand(
                        String.Format("exec proc_{0}Insert {1}", _tabela, ObterParametrosInserir(item)),
                        connection, transaction);

                    item.AtribuirChave(command.ExecuteScalar());
                }
                transaction.Commit();
            }

            return items;
        }

        public virtual void Apaga(T item)
        {
            using (SqlConnection connection = new SqlConnection(_ligacaoBD))
            {
                SqlCommand command = new SqlCommand(
                    String.Format("exec proc_{0}Delete {1}", _tabela, ObterParametrosApagar(item)),
                    connection);

                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }

        protected abstract String ObterParametrosInserir(T item);

        protected virtual String ObterParametrosActualizar(T item)
        {
            return ObterParametrosInserir(item);
        }
        
        protected abstract String ObterParametrosApagar(T item);

        protected abstract T CarregarDados(SqlDataReader dataReader, T item);
    }
}
